const express = require('express');
const sql = require('mssql');
const cors = require('cors');
const app = express();

// 配置CORS
app.use(cors());
app.use(express.json());

// SQL Server配置
const dbConfig = {
  user: 'sa',
  password: 'Admin123',
  server: 'localhost',
  database: 'study',
  options: {
    encrypt: false,
    trustServerCertificate: true
  }
};

// 连接数据库
let pool;

async function connectDB() {
  try {
    pool = await sql.connect(dbConfig);
    console.log('Database connected successfully');
  } catch (err) {
    console.error('Database connection failed:', err);
    // 重试连接
    setTimeout(connectDB, 5000);
  }
}

connectDB();

// API路由 - 获取日志数据
app.post('/api/logs', async (req, res) => {
  try {
    const { pSName, mlName, slName, deviceName, colorCode, roleName, roleLevel, oldEvent, newEvent, systemLevel, description, startDate, endDate, page = 1, pageSize = 10 } = req.body;
    const offset = (page - 1) * pageSize;
    
    // 构建SQL查询
    let query = `SELECT * FROM ESP_Logs WHERE 1=1`;   
    let params = [];
    
    if (pSName) {
      query += ` AND PSName LIKE @pSName`;
      params.push({ name: 'pSName', type: sql.VarChar, value: `%${pSName}%` });
    }
    
    if (mlName) {
      query += ` AND MLName LIKE @mlName`;
      params.push({ name: 'mlName', type: sql.VarChar, value: `%${mlName}%` });
    }
    
    if (slName) {
      query += ` AND SLName LIKE @slName`;
      params.push({ name: 'slName', type: sql.VarChar, value: `%${slName}%` });
    }
    
    if (deviceName) {
      query += ` AND DeviceName LIKE @deviceName`;
      params.push({ name: 'deviceName', type: sql.VarChar, value: `%${deviceName}%` });
    }
    

    if (colorCode) {
      query += ` AND ColorCode LIKE @colorCode`;
      params.push({ name: 'colorCode', type: sql.VarChar, value: `%${colorCode}%` });
    }
    
    if (roleName) {
      query += ` AND RoleName LIKE @roleName`;
      params.push({ name: 'roleName', type: sql.VarChar, value: `%${roleName}%` });
    }
    
    if (roleLevel) {
      query += ` AND RoleLevel LIKE @roleLevel`;
      params.push({ name: 'roleLevel', type: sql.VarChar, value: `%${roleLevel}%` });
    }
    
    if (oldEvent) {
      query += ` AND OldEvent LIKE @oldEvent`;
      params.push({ name: 'oldEvent', type: sql.VarChar, value: `%${oldEvent}%` });
    }
    
    if (newEvent) {
      query += ` AND NewEvent LIKE @newEvent`;
      params.push({ name: 'newEvent', type: sql.VarChar, value: `%${newEvent}%` });
    }
    
    if (systemLevel) {
      query += ` AND SystemLevel LIKE @systemLevel`;
      params.push({ name: 'systemLevel', type: sql.VarChar, value: `%${systemLevel}%` });
    }
    
    if (description) {
      query += ` AND Description LIKE @description`;
      params.push({ name: 'description', type: sql.VarChar, value: `%${description}%` });
    }

    if (startDate) {
      query += ` AND LogDateTime >= @startDate`;
      params.push({ name: 'startDate', type: sql.VarChar, value: startDate });
    }
    
    if (endDate) {
      query += ` AND LogDateTime <= @endDate`;
      params.push({ name: 'endDate', type: sql.VarChar, value: endDate });
    }
    
    // 添加排序和分页
    query += ` ORDER BY LogDateTime DESC OFFSET ${offset} ROWS FETCH NEXT ${pageSize} ROWS ONLY`; 
    
    // 查询数据
    let request = pool.request();
    // 添加参数到请求
    params.forEach(param => {
      request = request.input(param.name, param.type, param.value);
    });
    const result = await request.query(query);
    
    // 获取总记录数
    let countQuery = `SELECT COUNT(*) as total FROM ESP_Logs WHERE 1=1`;
    let countParams = [];
    
    if (pSName) {
      countQuery += ` AND PSName LIKE @pSName`;
      countParams.push({ name: 'pSName', type: sql.VarChar, value: `%${pSName}%` });
    }
    
    if (mlName) {
      countQuery += ` AND MLName LIKE @mlName`;
      countParams.push({ name: 'mlName', type: sql.VarChar, value: `%${mlName}%` });    
    }
    
    if (slName) {
      countQuery += ` AND SLName LIKE @slName`;
      countParams.push({ name: 'slName', type: sql.VarChar, value: `%${slName}%` });  
    }
    
    if (deviceName) {
      countQuery += ` AND DeviceName LIKE @deviceName`;
      countParams.push({ name: 'deviceName', type: sql.VarChar, value: `%${deviceName}%` });  
    }
    
    if (colorCode) {
      countQuery += ` AND ColorCode LIKE @colorCode`;
      countParams.push({ name: 'colorCode', type: sql.VarChar, value: `%${colorCode}%` });  
    }
    
    if (roleName) {
      countQuery += ` AND RoleName LIKE @roleName`;
      countParams.push({ name: 'roleName', type: sql.VarChar, value: `%${roleName}%` });
    }
    
    if (roleLevel) {
      countQuery += ` AND RoleLevel LIKE @roleLevel`;
      countParams.push({ name: 'roleLevel', type: sql.VarChar, value: `%${roleLevel}%` });
    }
    
    if (oldEvent) {
      countQuery += ` AND OldEvent LIKE @oldEvent`;
      countParams.push({ name: 'oldEvent', type: sql.VarChar, value: `%${oldEvent}%` });
    }
    
    if (newEvent) {
      countQuery += ` AND NewEvent LIKE @newEvent`;
      countParams.push({ name: 'newEvent', type: sql.VarChar, value: `%${newEvent}%` });
    }
    
    if (description) {
      countQuery += ` AND Description LIKE @description`;
      countParams.push({ name: 'description', type: sql.VarChar, value: `%${description}%` });
    }
    
    if (startDate) {
      countQuery += ` AND LogDateTime >= @startDate`;
      countParams.push({ name: 'startDate', type: sql.VarChar, value: startDate });
    }
    
    if (endDate) {
      countQuery += ` AND LogDateTime <= @endDate`; 
      countParams.push({ name: 'endDate', type: sql.VarChar, value: endDate });
    }
    
    // 执行总记录数查询
    let countRequest = pool.request();
    countParams.forEach(param => {
      countRequest = countRequest.input(param.name, param.type, param.value);
    });
    const countResult = await countRequest.query(countQuery);
    const total = countResult.recordset[0].total;
    
    res.json({
      data: result.recordset,
      total,
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      totalPages: Math.ceil(total / pageSize)
    });
  } catch (err) {
    console.error('Error fetching logs:', err);
    res.status(500).json({ error: 'Failed to fetch logs' });
  }
});

// API路由 - 导出日志数据
app.post('/api/logs/export', async (req, res) => {
  try {
    const { systemName, device, group1, group2, group3, userName, userLevel, oldEvent, newEvent, eventName, description, startDate, endDate } = req.body;
    
    // 构建SQL查询
    let query = `SELECT Id, SystemName, Device, Group1, Group2, Group3, UserName, UserLevel, OldEvent, NewEvent, Description, [DateTime] FROM paralog WHERE 1=1`;
    let params = [];
    
    if (systemName) {
      query += ` AND SystemName LIKE @systemName`;
      params.push({ name: 'systemName', type: sql.VarChar, value: `%${systemName}%` });
    }
    
    if (device) {
      query += ` AND Device LIKE @device`;
      params.push({ name: 'device', type: sql.VarChar, value: `%${device}%` });
    }
    
    if (group1) {
      query += ` AND Group1 LIKE @group1`;
      params.push({ name: 'group1', type: sql.VarChar, value: `%${group1}%` });
    }
    
    if (group2) {
      query += ` AND Group2 LIKE @group2`;
      params.push({ name: 'group2', type: sql.VarChar, value: `%${group2}%` });
    }
    
    if (group3) {
      query += ` AND Group3 LIKE @group3`;
      params.push({ name: 'group3', type: sql.VarChar, value: `%${group3}%` });
    }
    
    if (userName) {
      query += ` AND UserName LIKE @userName`;
      params.push({ name: 'userName', type: sql.VarChar, value: `%${userName}%` });
    }
    
    if (userLevel) {
      query += ` AND UserLevel LIKE @userLevel`;
      params.push({ name: 'userLevel', type: sql.VarChar, value: `%${userLevel}%` });
    }
    
    if (oldEvent) {
      query += ` AND OldEvent LIKE @oldEvent`;
      params.push({ name: 'oldEvent', type: sql.VarChar, value: `%${oldEvent}%` });
    }
    
    if (newEvent) {
      query += ` AND NewEvent LIKE @newEvent`;
      params.push({ name: 'newEvent', type: sql.VarChar, value: `%${newEvent}%` });
    }
    
    if (eventName) {
      query += ` AND (OldEvent LIKE @eventName OR NewEvent LIKE @eventName OR Description LIKE @eventName)`;
      params.push({ name: 'eventName', type: sql.VarChar, value: `%${eventName}%` });
    }
    
    if (description) {
      query += ` AND Description LIKE @description`;
      params.push({ name: 'description', type: sql.VarChar, value: `%${description}%` });
    }
    
    if (startDate) {
      query += ` AND [DateTime] >= @startDate`;
      params.push({ name: 'startDate', type: sql.VarChar, value: startDate });
    }
    
    if (endDate) {
      query += ` AND [DateTime] <= @endDate`;
      params.push({ name: 'endDate', type: sql.VarChar, value: endDate });
    }
    
    query += ` ORDER BY [DateTime] DESC`;
    
    // 执行查询
    let request = pool.request();
    params.forEach(param => {
      request = request.input(param.name, param.type, param.value);
    });
    const result = await request.query(query);
    
    // 设置响应头，允许前端下载CSV
    res.setHeader('Content-Type', 'text/csv');
    res.setHeader('Content-Disposition', 'attachment; filename="logs.csv"');
    
    // 生成CSV内容
    const headers = 'ID,SystemName,Device,Group1,Group2,Group3,UserName,UserLevel,OldEvent,NewEvent,Description,DateTime\n';
    const rows = result.recordset.map(row => 
      `${row.Id},"${row.SystemName || ''}","${row.Device || ''}","${row.Group1 || ''}","${row.Group2 || ''}","${row.Group3 || ''}","${row.UserName || ''}","${row.UserLevel || ''}","${row.OldEvent || ''}","${row.NewEvent || ''}","${row.Description || ''}","${row.DateTime || ''}"`
    ).join('\n');
    
    res.send(headers + rows);
  } catch (err) {
    console.error('Error exporting logs:', err);
    res.status(500).json({ error: 'Failed to export logs' });
  }
});

// 启动服务器
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

// 处理数据库连接错误
process.on('SIGINT', () => {
  sql.close().then(() => {
    console.log('Database connection closed');
    process.exit(0);
  });
});